package com.lxhll.personnel.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lxhll.personnel.entity.Department;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface DepartmentDao extends BaseMapper<Department> {


    /**
     * 统计选择月份的每个部门的总人数，默认为当前月
     * @param
     * @return
     */
    @Select("SELECT\n" +
            "\td.NAME,\n" +
            "\tcount(*) AS 'value' \n" +
            "FROM\n" +
            "\tdepartment d\n" +
            "\tLEFT JOIN employee e ON d.id = e.department_id \n" +
            "WHERE\n" +
            "\t e.is_resign = 0 \n" +
            "GROUP BY\n" +
            "\td.NAME")
    List<Map<String,Object>> statisticsEmployeeNumber();


    /**
     * 根据月份，人事月报
     * @param date
     * @return
     */
    @Select("SELECT\n" +
            "\td.`name` AS 部门,\n" +
            "\tsum(\n" +
            "\tIF\n" +
            "\t( DATE_FORMAT( e.entry_date, '%Y-%m' )< #{date}, 1, 0 )) AS 月初人数,\n" +
            "\tsum(\n" +
            "\tIF\n" +
            "\t( e.is_resign = 0, 1, 0 )) AS 月末人数,\n" +
            "\tsum(\n" +
            "\tIF\n" +
            "\t( DATE_FORMAT( e.entry_date, '%Y-%m' )= #{date}, 1, 0 )) AS 本月新入职,\n" +
            "\tsum(\n" +
            "\tIF\n" +
            "\t( e.is_resign = 1, 1, 0 )) AS 本月离职,\n" +
            "\tsum(\n" +
            "\tIF\n" +
            "\t( e.is_resign = 0 AND highest_education = '研究生', 1, 0 )) AS 研究生,\n" +
            "\tsum(\n" +
            "\tIF\n" +
            "\t( e.is_resign = 0 AND highest_education = '本科', 1, 0 )) AS 本科,\n" +
            "\tsum(\n" +
            "\tIF\n" +
            "\t( e.is_resign = 0 AND highest_education = '大专', 1, 0 )) AS 大专,\n" +
            "\tsum(\n" +
            "\tIF\n" +
            "\t( e.is_resign = 0 AND highest_education = '高中及以下', 1, 0 )) AS 高中及以下 \n" +
            "FROM\n" +
            "\tdepartment d\n" +
            "\tLEFT JOIN employee e ON e.department_id = d.id \n" +
            "GROUP BY\n" +
            "\td.id")
    List<Map<String,Object>> statisticsDetails(String date);

}
